import pymysql,subprocess,socket
import os,subprocess,re,threading
from pathlib import Path
class mysql_io:
    def __init__(self,host,user,passwd,db_name):
        self.host=host
        self.port=3306
        self.user=user
        self.password=passwd
        self.db_name=db_name

    #查询一条
    def select_one(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        cursor.execute(sql)
        data=cursor.fetchone()
        db.close()
        return data
    #查询多条
    def select_all(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        cursor.execute(sql)
        data=cursor.fetchall()
        db.close()
        return data

    #更新数据
    def update(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return "更新成功"
        except:
            db.rollback()
        db.close()

    def insert(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return "插入成功"
        except:
            db.rollback()
        db.close()


def fileio(filename):
    f=open(filename,"r")
    file_data=f.readlines()
    ip_list=[]
    for i in file_data:
        ip_list.append(i.strip())
    return ip_list

def ip_getmac(ipaddr):
    try:
        aa=subprocess.check_output("ping -n 1 %s"%ipaddr).decode("GBK")
        ss=subprocess.check_output("arp -a %s"%ipaddr).decode("GBK")
        return ss.split(ipaddr)[1].strip()[0:17]
    except:
        return "不在线，获取不到"

def ip_getname(ipaddr):
    HOSTNAME2 = socket.getfqdn(ipaddr)
    return ( HOSTNAME2)


def main(ip):
    pc_name=ip_getname(ip)
    my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
    sql2='''update ipdb.tp_ipdb set pc_name="%s" where ip="%s";'''%(pc_name,ip)
    print (sql2)
    my.update(sql2)



# #获取文件内信息入库
host="222.222.222.2"
user="root"
dbname="ipdb"
passwd="123456"
f=fileio("jishu1.txt")

def main_info_db(file_list):
    for i in file_list:
        ip_port_list=(i.split("/")[0].split(":"))
        if len(ip_port_list) ==2:
            ip=(ip_port_list[0]).strip()
            port=(ip_port_list[1].strip())
            iptag=ip.split(".")[3]
            mac=ip_getmac(ip)
        else:
            ip=(ip_port_list[0])
            mac=ip_getmac(ip)
            port=3389
        py=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        sql='''INSERT INTO `ipdb`.`tp_ipdb` ( `ip`, `port`, `mac`, `pc_name`, `ip_tag`, `tag`, `tag_name`, `ip_stau`, `on_off_status`, `datetime`) VALUES ( '%s', '%s', '%s', '4', '%s', '1', '2', '1','0  ', '2021-10-12');'''%(ip,port,mac,iptag)
        py.insert(sql)
        print ("写入成功")
        print (sql)

# 读取数据库中IP写入计算机名
main_info_db(fileio("jishu1.txt"))

my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
sql1='''select ip from  ipdb.tp_ipdb;'''
py=my.select_all(sql1)
ip_list=[]
for i in py:ip_list.append(i[0])
ts = [threading.Thread(target=main, args=(ip,)) for ip in ip_list]
for tt in ts:
    tt.start()
for tt in ts:
    tt.join()

